package com.changan.alm2.version.tools;

import com.opencsv.CSVWriter;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;

/**
 * <dependency>
 *             <groupId>com.opencsv</groupId>
 *             <artifactId>opencsv</artifactId>
 *             <version>5.6</version> <!-- 最新版本可能会有所变化 -->
 *         </dependency>
 */
public class ExportToCSV {

    public static void main(String[] args) {
        String url = "jdbc:sqlserver://1.116.42.22:1433;databaseName=test";
        String username = "sa";
        String password = "Aa123456";

        try (Connection connection  = DriverManager.getConnection(url, username, password);
) {
            DatabaseMetaData metaData = connection.getMetaData();
            Statement statement = connection.createStatement();

            ResultSet tables = statement.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");

            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                exportTableToCSV(connection, tableName);
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void exportTableToCSV(Connection connection, String tableName) {
        try (Statement statement = connection.createStatement()) {
//            String path = ExportToCSV.class.getResource("dbdata").getPath();
            String path = System.getProperty("user.dir");

            ResultSet resultSet = statement.executeQuery("SELECT * FROM [" + tableName+"]");

            try (CSVWriter csvWriter = new CSVWriter(new FileWriter(path+"/dbdata/"+tableName + ".csv"))) {
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                String[] headers = new String[columnCount];
                for (int i = 1; i <= columnCount; i++) {
                    headers[i - 1] = metaData.getColumnName(i);
                }
                csvWriter.writeNext(headers);

                while (resultSet.next()) {
                    String[] row = new String[columnCount];
                    for (int i = 1; i <= columnCount; i++) {
                        row[i - 1] = resultSet.getString(i);
                    }
                    csvWriter.writeNext(row);
                }
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}
